package com.smartao.core.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
import java.util.zip.GZIPInputStream;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;


/**
 * 数据库操作工具
 * @author Ricky
 */
@Component
public class DatabaseMojo {

	protected Logger logger = LoggerFactory.getLogger(getClass());

	/**
	 * The batch size when executing batches.
	 * @parameter default-value="20"
	 * @required
	 */
	private int batchSize = 20;

	/**
	 * Whether or not to use SQL batches.
	 * @parameter default-value="true"
	 * @required
	 */
	private boolean useBatch = true;

	/**
	 * The String that delimits sql statements in a file.
	 * @parameter default-value=";"
	 * @required
	 */
	private String sqlDelimiter = ";";

	/**
	 * Executes all of the sql scripts in a given directory
	 * using the given database connection.
	 * @param directory the directory where the scripts reside
	 * @param con the database connection
	 * @throws SQLException on error
	 * @throws IOException on error
	 */
	public void executeScriptsInDirectory(File directory, Connection con) throws SQLException, IOException {

		logger.info("Executing scripts in: " + directory.getName());

		if (!directory.isDirectory()) {
			throw new IOException(directory.getName() + " is not a directory");
		}

		File[] files = directory.listFiles();

		// sort
		Arrays.sort(files, new Comparator<File>() {
			@Override
			public int compare(File arg0, File arg1) {
				return (arg0).getName().compareTo((arg1).getName());
			}
		});

		// loop through all the files and execute them
		for (int i = 0; i < files.length; i++) {
			if (!files[i].isDirectory() && files[i].isFile()) {
				double startTime = System.currentTimeMillis();
				if (useBatch) {
					batchExecuteSqlScript(files[i], con);
				} else {
					executeSqlScript(files[i], con);
				}
				double endTime = System.currentTimeMillis();
				double elapsed = ((endTime - startTime) / 1000.0);
				logger.info(" script completed execution in " + elapsed + " second(s)");
			}
		}
	}

	/**
	 * Batch executes a script file.
	 * @param file the file
	 * @param con the connection
	 * @throws SQLException on error
	 * @throws IOException on error
	 */
	public void batchExecuteSqlScript(File file, Connection con) throws SQLException, IOException {

		logger.info("batch executing script: " + file.getName());

		if (!file.exists() || !file.canRead() || file.isDirectory() || !file.isFile()) {
			throw new IOException(file.getName() + " is not a file");
		}

		InputStream ips = new FileInputStream(file);

		// if it's a compressed file (gzip) then unzip as
		// we read it in
		if (file.getName().toUpperCase().endsWith("GZ")) {
			ips = new GZIPInputStream(ips);
		}

		// our file reader
		Reader reader;
		reader = new InputStreamReader(ips);

		// create SQL Statement
		Statement st = con.createStatement();

		StringBuffer sql = new StringBuffer();
		String line;
		BufferedReader in = new BufferedReader(reader);

		// loop through the statements
		int execCount = 0;
		List<String> sqlLines = new ArrayList<String>();
		while ((line = in.readLine()) != null) {

			// append the line
			line.trim();
			sql.append("\n").append(line);

			// if the line ends with the delimiter, then
			// lets execute it
			if (sql.toString().endsWith(sqlDelimiter)) {
				String sqlLine = sql.substring(0, sql.length() - sqlDelimiter.length());
				sqlLines.add(sqlLine);
				sql.replace(0, sql.length(), "");
				execCount++;
				if (sqlLines.size() >= batchSize) {
					executeBatch(st, sqlLines);
					sqlLines.clear();
				}
			}
		}

		// execute last statement
		if (sql.toString().trim().length() > 0) {
			sqlLines.add(sql.toString());
			sql.replace(0, sql.length(), "");
			execCount++;
			executeBatch(st, sqlLines);
			sqlLines.clear();
		} else if (sqlLines.size() > 0) {
			executeBatch(st, sqlLines);
			sqlLines.clear();
		}

		st.close();
		reader.close();
		in.close();

		logger.info(" " + execCount + " statements batch executed from " + file.getName());
	}

	/**
	 * Executes the given sql script, using the given connection.
	 */
	public void executeSqlScript(File file, Connection con) throws SQLException, IOException {
		logger.info("executing script: " + file.getName());

		if (!file.exists() || !file.canRead() || file.isDirectory() || !file.isFile()) {
			throw new IOException(file.getName() + " is not a file");
		}
		InputStream ips = new FileInputStream(file);
	
		if (file.getName().toUpperCase().endsWith("GZ")) {
			ips = new GZIPInputStream(ips);
			logger.info(" file is gz compressed, using gzip stream");
		}
		
		executeSqlScrit(ips,con);
	}
	
	public void executeSqlScrit(InputStream ips,Connection con) throws SQLException, IOException{
		Reader reader= new InputStreamReader(ips);

		Statement st = con.createStatement();

		StringBuffer sql = new StringBuffer();
		BufferedReader in = new BufferedReader(reader);

		String line;
		int execCount = 0;
		while ((line = in.readLine()) != null) {
			line.trim();
			sql.append("\n").append(line);

			// if the line ends with the delimiter, then lets execute it
			if (sql.toString().endsWith(sqlDelimiter)) {
				String sqlLine = sql.substring(0, sql.length() - sqlDelimiter.length());
				executeStatement(st, sqlLine);
				sql.replace(0, sql.length(), "");
				execCount++;
			}
		}

		// execute last statement
		if (sql.toString().trim().length() > 0) {
			executeStatement(st, sql.toString());
			sql.replace(0, sql.length(), "");
			execCount++;
		}

		st.close();
		reader.close();
		in.close();

		logger.info(" " + execCount + " statements executed.");
	}

	/**
	 * Executes a batch update.
	 * @param st the statement
	 * @param sqlLines the sql lines
	 * @throws SQLException on error
	 */
	protected void executeBatch(Statement st, List<String> sqlLines) throws SQLException {

		if (logger.isDebugEnabled()) {
			logger.debug("Executing batch");
		}

		// add to batch
		for (int i = 0; i < sqlLines.size(); i++) {
			st.addBatch(sqlLines.get(i));
		}

		int[] ret = st.executeBatch();
		if (logger.isDebugEnabled()) {
			logger.debug("    " + ret.length + " statement(s) executed");
		}

		for (int i = 0; i < ret.length; i++) {
			if (ret[i] == Statement.SUCCESS_NO_INFO && logger.isDebugEnabled()) {
				logger.debug("    statement " + i + " processed successfully " + "without return results");

			} else if (ret[i] == Statement.EXECUTE_FAILED) {
				logger.error("    error durring batch execution of statement: " + sqlLines.get(i));
				throw new SQLException("Error executing: " + sqlLines.get(i));

			} else if (ret[i] >= 0 && logger.isDebugEnabled()) {
				logger.debug("    statement " + i + " processed successfully " + " with " + ret[i]
						+ " records effected");
			}
		}

	}

	/**
	 * Runs the given SQL statement.
	 * @param st the statement to run it on
	 * @param sqlLine the sql statement
	 * @throws SQLException on error
	 */
	protected void executeStatement(Statement st, String sqlLine) throws SQLException {
		if (logger.isDebugEnabled()) {
			logger.debug("    executing:\n" + sqlLine);
		}
		boolean execRet = false;
		try {
			execRet = st.execute(sqlLine);
		} catch (SQLException sqle) {
			SQLException se = new SQLException(sqle.getMessage() + "\n\nSQL:\n" + sqlLine, sqle.getSQLState(),
					sqle.getErrorCode());
			se.setNextException(sqle);
			throw se;
		}
		boolean loop = true;
		while (loop) {
			if (execRet) {
				logger.warn(" statement returned a resultset");
			} else {
				// Got an update count
				int count = st.getUpdateCount();

				if (count == -1) {
					// Nothing left
					loop = false;
				} else if (logger.isDebugEnabled()) {
					// An update count was returned
					logger.debug("    " + count + " row(s) updated");
				}
			}
			if (loop) {
				execRet = st.getMoreResults();
			}
		}
	}

	public void setBatchSize(int batchSize) {
		this.batchSize = batchSize;
	}

	public void setUseBatch(boolean useBatch) {
		this.useBatch = useBatch;
	}
}